package cn.iocoder.yudao.module.system.dal.mysql.userbehavior;

import cn.iocoder.yudao.framework.common.pojo.PageParam;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.userbehavior.UserFeaturePathDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 用户功能路径 Mapper
 *
 * @author 系统开发团队
 */
@Mapper
public interface UserFeaturePathMapper extends BaseMapperX<UserFeaturePathDO> {

    /**
     * 根据用户ID和时间范围查询功能路径
     */
    default List<UserFeaturePathDO> selectByUserIdAndTimeRange(Long userId, LocalDateTime startTime, LocalDateTime endTime) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .eq(UserFeaturePathDO::getUserId, userId)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .orderByDesc(UserFeaturePathDO::getPathStartTime));
    }

    /**
     * 根据会话ID查询功能路径
     */
    default UserFeaturePathDO selectBySessionId(String sessionId) {
        return selectOne(UserFeaturePathDO::getSessionId, sessionId);
    }

    /**
     * 查询指定时间范围内的所有路径
     */
    default List<UserFeaturePathDO> selectByTimeRange(LocalDateTime startTime, LocalDateTime endTime) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .orderByDesc(UserFeaturePathDO::getPathStartTime));
    }

    /**
     * 统计路径完成状态分布
     */
    default List<Map<String, Object>> countCompletionStatusDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getCompletionStatus)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .groupBy(UserFeaturePathDO::getCompletionStatus)
                .last("SELECT completion_status, COUNT(*) as pathCount"));
    }

    /**
     * 统计功能数量分布
     */
    default List<Map<String, Object>> countFeatureCountDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getFeatureCount)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .groupBy(UserFeaturePathDO::getFeatureCount)
                .orderByAsc(UserFeaturePathDO::getFeatureCount)
                .last("SELECT feature_count, COUNT(*) as pathCount"));
    }

    /**
     * 统计入口功能分布
     */
    default List<Map<String, Object>> countEntryFeatureDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getEntryFeature)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .isNotNull(UserFeaturePathDO::getEntryFeature)
                .groupBy(UserFeaturePathDO::getEntryFeature)
                .last("SELECT entry_feature, COUNT(*) as pathCount ORDER BY pathCount DESC"));
    }

    /**
     * 统计出口功能分布
     */
    default List<Map<String, Object>> countExitFeatureDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getExitFeature)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .isNotNull(UserFeaturePathDO::getExitFeature)
                .groupBy(UserFeaturePathDO::getExitFeature)
                .last("SELECT exit_feature, COUNT(*) as pathCount ORDER BY pathCount DESC"));
    }

    /**
     * 统计转化率
     */
    default BigDecimal selectConversionRate(LocalDateTime startTime, LocalDateTime endTime) {
        Long totalPaths = selectCount(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime));
        
        Long convertedPaths = selectCount(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .eq(UserFeaturePathDO::getConversionAchieved, true));
        
        if (totalPaths == 0) {
            return BigDecimal.ZERO;
        }
        
        return BigDecimal.valueOf(convertedPaths)
                .divide(BigDecimal.valueOf(totalPaths), 4, BigDecimal.ROUND_HALF_UP)
                .multiply(BigDecimal.valueOf(100));
    }

    /**
     * 统计平均跳出率
     */
    default BigDecimal selectAvgBounceRate(LocalDateTime startTime, LocalDateTime endTime) {
        // 需要使用原生SQL实现
        return BigDecimal.ZERO; // 占位实现
    }

    /**
     * 统计平均参与度评分
     */
    default BigDecimal selectAvgEngagementScore(LocalDateTime startTime, LocalDateTime endTime) {
        // 需要使用原生SQL实现
        return BigDecimal.ZERO; // 占位实现
    }

    /**
     * 统计平均满意度评分
     */
    default BigDecimal selectAvgSatisfactionScore(LocalDateTime startTime, LocalDateTime endTime) {
        // 需要使用原生SQL实现
        return BigDecimal.ZERO; // 占位实现
    }

    /**
     * 统计平均路径时长
     */
    default Double selectAvgPathDuration(LocalDateTime startTime, LocalDateTime endTime) {
        // 需要使用原生SQL实现
        return 0.0; // 占位实现
    }

    /**
     * 查询热门路径
     */
    default List<Map<String, Object>> selectPopularPaths(LocalDateTime startTime, LocalDateTime endTime, Integer limit) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getFeaturePath)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .groupBy(UserFeaturePathDO::getFeaturePath)
                .last("SELECT feature_path, COUNT(*) as pathCount ORDER BY pathCount DESC LIMIT " + (limit != null ? limit : 10)));
    }

    /**
     * 查询高转化路径
     */
    default List<UserFeaturePathDO> selectHighConversionPaths(LocalDateTime startTime, LocalDateTime endTime, 
                                                               BigDecimal minConversionRate) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .eq(UserFeaturePathDO::getConversionAchieved, true)
                .orderByDesc(UserFeaturePathDO::getEngagementScore));
    }

    /**
     * 查询长路径用户
     */
    default List<UserFeaturePathDO> selectLongPathUsers(LocalDateTime startTime, LocalDateTime endTime, 
                                                         Integer minFeatureCount) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .ge(UserFeaturePathDO::getFeatureCount, minFeatureCount)
                .orderByDesc(UserFeaturePathDO::getFeatureCount));
    }

    /**
     * 查询短路径用户（可能的跳出用户）
     */
    default List<UserFeaturePathDO> selectShortPathUsers(LocalDateTime startTime, LocalDateTime endTime, 
                                                          Integer maxFeatureCount) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .le(UserFeaturePathDO::getFeatureCount, maxFeatureCount)
                .orderByAsc(UserFeaturePathDO::getFeatureCount));
    }

    /**
     * 统计用户类型分布
     */
    default List<Map<String, Object>> countUserTypeDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getUserType)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .groupBy(UserFeaturePathDO::getUserType)
                .last("SELECT user_type, COUNT(DISTINCT user_id) as userCount"));
    }

    /**
     * 统计设备类型分布
     */
    default List<Map<String, Object>> countDeviceTypeDistribution(LocalDateTime startTime, LocalDateTime endTime) {
        return selectMaps(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .select(UserFeaturePathDO::getDeviceType)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .groupBy(UserFeaturePathDO::getDeviceType)
                .last("SELECT device_type, COUNT(*) as pathCount"));
    }

    /**
     * 删除过期数据
     */
    default int deleteExpiredData(LocalDateTime beforeTime) {
        return delete(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .lt(UserFeaturePathDO::getPathStartTime, beforeTime));
    }

    /**
     * 分页查询用户功能路径
     */
    default PageResult<UserFeaturePathDO> selectPage(Long userId, Integer userType, Integer completionStatus,
                                                      Boolean conversionAchieved, LocalDateTime startTime, 
                                                      LocalDateTime endTime, int pageNo, int pageSize) {
        PageParam pageParam = new PageParam();
        pageParam.setPageNo(pageNo);
        pageParam.setPageSize(pageSize);
        return selectPage(pageParam, new LambdaQueryWrapperX<UserFeaturePathDO>()
                .eq(userId != null, UserFeaturePathDO::getUserId, userId)
                .eq(userType != null, UserFeaturePathDO::getUserType, userType)
                .eq(completionStatus != null, UserFeaturePathDO::getCompletionStatus, completionStatus)
                .eq(conversionAchieved != null, UserFeaturePathDO::getConversionAchieved, conversionAchieved)
                .between(startTime != null && endTime != null, UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .orderByDesc(UserFeaturePathDO::getPathStartTime));
    }

    /**
     * 查询用户最近路径
     */
    default List<UserFeaturePathDO> selectRecentPaths(Long userId, Integer limit) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .eq(UserFeaturePathDO::getUserId, userId)
                .orderByDesc(UserFeaturePathDO::getPathStartTime)
                .last("LIMIT " + (limit != null ? limit : 10)));
    }

    /**
     * 查询相似路径
     */
    default List<UserFeaturePathDO> selectSimilarPaths(String featurePath, LocalDateTime startTime, 
                                                        LocalDateTime endTime, Integer limit) {
        return selectList(new LambdaQueryWrapperX<UserFeaturePathDO>()
                .like(UserFeaturePathDO::getFeaturePath, featurePath)
                .between(UserFeaturePathDO::getPathStartTime, startTime, endTime)
                .orderByDesc(UserFeaturePathDO::getEngagementScore)
                .last("LIMIT " + (limit != null ? limit : 10)));
    }
}